#include "form_recipeacount.h"
#include "ui_form_recipeacount.h"
#include <QSettings>
#include <QAxObject>
#include <QAxWidget>
#include "pub_var.h"
#include <QMessageBox>
#include <QSqlError>
#include <QtDebug>
#include <QFileInfo>

Form_recipeAcount::Form_recipeAcount(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Form_recipeAcount)
{
    ui->setupUi(this);

    //窗口位置
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QRect rct = setIni.value("windowState/cftj").toRect();
    this->setGeometry(rct);

    //默认日期
    QDate dt;
    ui->deStart->setDate(dt.currentDate());
    ui->deEnd->setDate(dt.currentDate());

    //数据库连接
    qro1 = new QSqlQuery(pub::db_o);
    qro2 = new QSqlQuery(pub::db_o);

    //报表打印机
    bzPrinter = setIni.value("bz/printer").toString();
}

Form_recipeAcount::~Form_recipeAcount()
{
    delete ui;
}

void Form_recipeAcount::closeEvent(QCloseEvent *event)
{
    Q_UNUSED(event);
    QSettings setIni("clin.ini", QSettings::IniFormat);
    setIni.setValue("windowState/cftj", QVariant(this->geometry()));
}

void Form_recipeAcount::on_pbExit_clicked()
{
    close();
}

void Form_recipeAcount::on_deStart_dateChanged(const QDate &date)
{
    ui->deEnd->setDate(date);
}

void Form_recipeAcount::on_pbGenerate_clicked()
{
    ui->pbGenerate->setEnabled(false);
    qApp->processEvents();  //刷新显示
    if(ui->deStart->date() > ui->deEnd->date()){
        QMessageBox::warning(this, "注意","起始日期不能大于终止日期！");
        ui->pbGenerate->setEnabled(true);
        return;
    }
    QString dt1 = ui->deStart->date().toString("yyyy/MM/dd");
    QString dt2 = ui->deEnd->date().toString("yyyy/MM/dd");
    QString sq;

    //处方统计

    if(ui->rbSffs->isChecked()){
        int yearS = ui->deStart->date().year();
        int yearE = ui->deEnd->date().year();
        int monthS = ui->deStart->date().month();
        int monthE = ui->deEnd->date().month();
        int dayS = ui->deStart->date().day();
        int dayE = ui->deEnd->date().day();

        if(monthS != monthE || yearS != yearE){
            QMessageBox::warning(this, "注意","按收费类别统计处方不可以跨月份！");
            ui->pbGenerate->setEnabled(true);
            return;
        }

        int dayCount = dayE - dayS + 1 ;  //统计天数
        QString titleStr = QString::number(yearS) + "年" + QString::number(monthS)+"月";
        titleStr.append("处方收费分类统计表");
        //处方数量
        sq = "select to_char(i.oper_date, 'dd') d, count(distinct r.recipe_no),"
             " r.dept_code,i.pact_code "
             " from fin_opb_invoiceinfo i, pha_sto_recipe r "
             " where i.invoice_no = r.invoice_no and trunc(i.oper_date) >= to_date('"
             + dt1 + "','yyyy/mm/dd') and trunc(i.oper_date) <= to_date('"
             + dt2 + "','yyyy/mm/dd') "
             "group by to_char(i.oper_date, 'dd'), i.pact_code, r.dept_code order by d";

        if(!qro1->exec(sq)){
            QMessageBox::warning(this, "注意", qro1->lastError().text());
            qDebug()<< qro1->lastError().text();
            qDebug()<< qro1->lastQuery();
            ui->pbGenerate->setEnabled(true);
            return;
        }

        //金额

        sq = "select d, dpt, pact_code, sum(pay_cost), sum(own_cost) from"
             " (select to_char(i.oper_date, 'dd') d,( i.invoice_no),i.pact_code ,"
             " (i.pay_cost),(i.own_cost),"
             " (select r.dept_code from pha_sto_recipe r where r.invoice_no = i.invoice_no "
             " and rownum = 1) dpt from fin_opb_invoiceinfo i "
             " where trunc(i.oper_date) >= to_date('"+ dt1 + "','yyyy/mm/dd') "
             " and trunc(i.oper_date) <= to_date('"+ dt2 + "','yyyy/mm/dd') )"
                                                                                                           " group by d, pact_code,  dpt  order by d";
        if(!qro2->exec(sq)){
            QMessageBox::warning(this, "注意", qro2->lastError().text());
            qDebug()<< qro2->lastError().text();
            qDebug()<< qro2->lastQuery();
            ui->pbGenerate->setEnabled(true);
            return;
        }
        if(!qro2->next() && !qro1->next()){
            QMessageBox::warning(this, "注意", "指定日期范围无数据！");
            ui->pbGenerate->setEnabled(true);
            return;
        }

        QAxObject *excel;   //操作excel用于打印输出

        try{
            excel = new QAxObject("Excel.Application"); //
        }
        catch(...){
            try{
                excel = new QAxObject("ket.Application"); //
            }
            catch(...){
                QMessageBox::information(this,"错误","电子表启动失败！");
                return;
            }
        }

        QString fileName = QCoreApplication::applicationDirPath();
        fileName.append("/recipe_count.xls");

        QFileInfo info(fileName);
        if(!info.exists()){
            QMessageBox::information(this,"注意", fileName + " 文件路径无效！");
            return;
        }

        //excel->dynamicCall("SetVisible (bool Visible)", "false"); // 设置为不显示窗体
        //excel->setProperty("DisplayAlerts", false); //不显示任何警告信息

        QAxObject *wkBooks = excel->querySubObject("Workbooks");
        QAxObject *book =  wkBooks->querySubObject("Open(const QString&)", info.absoluteFilePath());
        QAxObject *wkSheets = book->querySubObject("Sheets");
        QAxObject *sheet = wkSheets->querySubObject("Item(int)", 1);
        QAxObject *cell  = new QAxObject ;


        //标题
        cell = sheet->querySubObject("Cells(int, int)", 1,  1);
        cell->setProperty("Value", titleStr);
        //清空表格

        int x,y;
        for(x = 5; x < 36; x++){
            for(y = 1; y < 15; y++){
                if(y == 5 || y == 8 || y == 12){
                    ;  // 列包含公式
                }else{
                    cell = sheet->querySubObject("Cells(int, int)", x,  y);
                    cell->setProperty("Value", "");
                }
            }
        }
        x=5;
        y=1;

        int day,count;//日期，处方
        QString department,mod;//科室，收费方式
        double pay,own;//账户金额，现金金额

        //处方数量
        while(true){

            day = qro1->value(0).toInt();//日期
            count = qro1->value(1).toInt(); //处方数量
            department = qro1->value(2).toString(); //科室
            mod = qro1->value(3).toString(); //收费方式

            x = day + 4 ;//由天数确定行数
            cell = sheet->querySubObject("Cells(int, int)", x,  1);
            cell->setProperty("Value", QString::number(day));

            if(department == "0026" && mod == "1"){
                y = 6;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::number(count));

            }else if(department == "0026" && mod == "2"){
                y = 2;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::number(count));

            }else if(department == "0028" && mod == "1"){
                y = 13;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::number(count));

            }else if(department == "0028" && mod == "2"){
                y = 9;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::number(count));

            }else{
                //QMessageBox::information(this,"错误","数据分项类别错误！"+department + " - " + mod);

            }

            if(!qro1->next()){
                break;
            }
        }


        //金额
        while(true){

            day = qro2->value(0).toInt();//日期
            department = qro2->value(1).toString(); //科室
            mod = qro2->value(2).toString(); //收费方式
            pay = qro2->value(3).toDouble(); //账户支付
            own = qro2->value(4).toDouble(); //现金支付

            x = day + 4 ;//由天数确定行数
            cell = sheet->querySubObject("Cells(int, int)", x,  1);
            cell->setProperty("Value", QString::number(day));

            if(department == "0026" && mod == "1"){
                y = 7;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",own));
            }else if(department == "0026" && mod == "2"){
                y = 3;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",pay));
                y = 4;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",own));
            }else if(department == "0028" && mod == "1"){
                y = 14;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",own));
            }else if(department == "0028" && mod == "2"){
                y = 10;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",pay));
                y = 11;
                cell = sheet->querySubObject("Cells(int, int)", x,  y);
                cell->setProperty("Value", QString::asprintf("%.2f",own));
            }else{
                //QMessageBox::information(this,"错误","数据分项类别错误！"+department + " - " + mod);

            }

            if(!qro2->next()){
                break;
            }
        }


        book->dynamicCall("Save()");   //保存文件
        book->setProperty("ActivePrinter", bzPrinter);
        book->dynamicCall("PrintOut()");   //打印输出
        excel->dynamicCall("Quit()"); // 关闭excel
        book->dynamicCall("Close(Boolean)", false);  // 关闭文件

        QMessageBox::information(this,"注意","打印输出完毕！");

/*
        //收据号关联的退费项目
        QString invo_no = pub::add0(ui->leSjh->text().trimmed(),12);
        qro->exec("select a.item_name trade_name , a.specs, a.price_unit pack_unit,  "
                  " a.pqty qty, concat( concat(a.own_cost/a.pqty  ,'/'), a.price_unit) prs"
                  " from view_feedetail a  where invoice_no = '" + invo_no + "'"
                  " and qty >0 order by od ");

        QString tmpit;
        while(qro->next()){

            tmpit = qro->value("trade_name").toString();
            tmpit.append("("+qro->value("prs").toString()+")");
            cell=sheet->querySubObject("Cells(int, int)",x,y);
            cell->setProperty("Value",tmpit);
            y++;

            tmpit = qro->value("specs").toString();
            cell = sheet->querySubObject("Cells(int, int)",x,y);
            cell->setProperty("Value",tmpit);
            y++;

            tmpit=qro->value("qty").toString();
            tmpit = tmpit + qro->value("pack_unit").toString();
            cell = sheet->querySubObject("Cells(int, int)",x,y);
            cell->setProperty("Value",tmpit);
            y++;

            x ++ ;
            y = 2;
        }

        //患者姓名、就诊日期
        sq = "select name,to_char(reg_date,'yyyy-mm-dd') "
                     " from fin_opr_register where clinic_code = '"+ clinicCode +"'";
        qro->exec(sq);
        qro->next();
        tmpit = "患者:" + qro->value(0).toString() + "    发票号:" + invo_no;
        tmpit.append("    就诊日期:" + qro->value(1).toString());
        cell = sheet->querySubObject("Cells(int, int)", 3, 2);
        cell->setProperty("Value",tmpit);

        tmpit = "退药日期:" + QDate::currentDate().toString("yyyy-MM-dd");
        cell = sheet->querySubObject("Cells(int, int)",3,6);
        cell->setProperty("Value",tmpit);

        //收费类别、金额
        qro->exec("select pact_name, own_cost, pay_cost from fin_opb_invoiceinfo "
                  "where invoice_no = '" + invo_no + "'");
        qro->next();
        double yb,xj;   //医保金额，现金金额
        yb = qro->value("pay_cost").toDouble();
        xj = qro->value("own_cost").toDouble();
        tmpit = qro->value("pact_name").toString();
        if(tmpit == "现金"){
            tmpit = tmpit + "，支付金额：" + QString::asprintf("%.2f", xj);
        }else{
            tmpit = "医保，卡：" +  QString::asprintf("%.2f", yb);
            tmpit = tmpit + "，现金："+QString::asprintf("%.2f", xj);
        }
        cell = sheet->querySubObject("Cells(int, int)",12,2);
        cell->setProperty("Value",tmpit);

        tmpit = "医生签字：" + doctName;
        cell = sheet->querySubObject("Cells(int, int)",12,3);
        cell->setProperty("Value",tmpit);


*/

    }else if(ui->rbYpfl->isChecked()){
        /*
            qr0.exec("delete from sun_recipe_count");    //清除旧数据
            cfSql = "select '皮肤科中药口服' lb,count(distinct o.recipe_no) ct,to_char(o.oper_date, 'yyyy-mm-dd') dt "
                    " from pha_com_output o, drug_base_info b where o.drug_code = b.drug_code  and "
                    " trunc(oper_date) >=to_date('" + dt1 + "','yyyy/mm/dd')  and trunc(oper_date) <= to_date('"
                    + dt2 + "','yyyy/mm/dd') and department = '皮肤' and useage = '口服' and  medicine_type = 'Z' "
                    "group by to_char(o.oper_date, 'yyyy-mm-dd')  "
                    "  union  "
                    " select '皮肤科其它' lb,count(distinct o.recipe_no) ct,to_char(o.oper_date, 'yyyy-mm-dd') dt "
                    " from pha_com_output o, drug_base_info b where o.drug_code = b.drug_code and o.recipe_no in"
                    " (  ( select distinct o.recipe_no from pha_com_output o, drug_base_info b"
                    "  where o.drug_code = b.drug_code  and trunc(oper_date) >=to_date('" + dt1 + "','yyyy/mm/dd')  "
                    " and trunc(oper_date) <= to_date('" + dt2 + "','yyyy/mm/dd') "
                    " and department = '皮肤' and o.out_num > 0)"
                    "    minus "
                    "    (select  distinct o.recipe_no from pha_com_output o, drug_base_info b "
                    "     where o.drug_code = b.drug_code and trunc(oper_date) >=to_date('" + dt1 + "','yyyy/mm/dd')"
                    "  and trunc(oper_date) <= to_date('" + dt2 + "','yyyy/mm/dd')"
                    "  and department = '皮肤' and useage = '口服' and  medicine_type = 'Z') )"
                    "  group by to_char(o.oper_date, 'yyyy-mm-dd')   "
                    " union  "
                    " select '骨科' lb, count(distinct o.recipe_no) ct,to_char(o.oper_date, 'yyyy-mm-dd') dt "
                    " from pha_com_output o, drug_base_info b"
                    " where o.drug_code = b.drug_code  and trunc(oper_date) >=to_date('" + dt1 + "','yyyy/mm/dd') "
                    " and trunc(oper_date) <= to_date('" + dt2 + "','yyyy/mm/dd') "
                    " and department = '骨科' group by to_char(o.oper_date, 'yyyy-mm-dd') "
                    " order by dt ";
            if(!qr.exec(cfSql)){
                qDebug()<<cfSql;
            }
        qDebug()<<cfSql;
            int pz,pq,gs;
            pz = 0;
            pq = 0;
            gs = 0;
            QString dt,odt,lb;
            while(qr.next()){

                dt = qr.value(2).toString();
                lb = qr.value(0).toString();
                if(odt.isEmpty()){
                    odt = dt;
                }
                if(dt != odt ){
                    qr0.exec("insert into sun_recipe_count (dt, pz, pq, gs)"
                             " values ('" + odt + "',"
                             + QString::number(pz) + ","
                             + QString::number(pq) + ","
                             + QString::number(gs) + ") ");
                    pz = 0;
                    pq = 0;
                    gs = 0;
                    odt = dt;
                    qDebug()<<qr0.lastQuery();
                }
                if(lb =="皮肤科中药口服" ){
                    pz = qr.value(1).toInt();
                }else if(lb =="皮肤科其它" ){
                    pq = qr.value(1).toInt();
                }else if(lb =="骨科" ){
                    gs = qr.value(1).toInt();
                }
                qDebug()<<dt<<lb<<pz<<pq<<gs;
            }

            qr0.exec("insert into sun_recipe_count (dt, pz, pq, gs)"
                     " values ('" + dt + "',"
                     + QString::number(pz) + ","
                     + QString::number(pq) + ","
                     + QString::number(gs) + ") ");
            qDebug()<<qr0.lastQuery();

            qr0.exec("select sum(pz),sum(pq),sum(gs) from sun_recipe_count");
            qr0.next();
            pz = qr0.value(0).toInt();
            pq = qr0.value(1).toInt();
            gs = qr0.value(2).toInt();
            qr0.exec("insert into sun_recipe_count (dt, pz, pq, gs)"
                     " values ('合计',"
                     + QString::number(pz) + ","
                     + QString::number(pq) + ","
                     + QString::number(gs) + ") ");

            qDebug()<<qr0.lastQuery();
            cfSql = "select dt, pz, pq, gs, pz + pq + gs from sun_recipe_count order by dt ";
        */

    }
    ui->pbGenerate->setEnabled(true);
}
